<?php

declare(strict_types=1);

namespace app\common\tool\excel;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PHPExcel;


class Export
{
    /*
     * 把导出为excel到浏览器并下载
     * $title:标题
     * $headArr: 表头
     * $data: 数据
     * $fileName:文件名
     * */
    public static function excelExport($headArr = [], $data = [], $fileName = '')
    {
        
        ini_set('memory_limit', '1024M');
        set_time_limit(3600);
        $spreadsheet    = new Spreadsheet();

        $objPHPExcel    = $spreadsheet->getActiveSheet();
        // $objPHPExcel->setTitle($title);
        // $objPHPExcel->setCellValueByColumnAndRow(1, 1, $title);
        // 设置表头
        $key = ord("A");
        foreach ($headArr as $v) {
            $colum = chr($key);
            $objPHPExcel->setCellValue($colum . '1', $v);
            $key += 1;
        }

        $column = 2;
        foreach ($data as $key => $rows) { // 行写入
            $span = ord("A");
            foreach ($rows as $keyName => $value) { // 列写入
                $objPHPExcel->setCellValue(chr($span) . $column, $value);
                $span++;
            }
            $column++;
        }

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $fileName . '"');
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        //删除临时的sheet
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }

    /**
     * 导出到excel
     * @author Liu Yuanhang
     * @date 2021-08-28
     * @param $expTitle 文件标题
     * @param $expTableData 数据
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    public static function exportExcel_1($expTitle, $expTableData)
    {
        // print('<pre>');print_r($expTableData);die;
        ini_set('memory_limit', '1024M');
        set_time_limit(3600);
        $fileName = $expTitle . date('_YmdHis'); //or $xlsTitle 文件名称可根据自己情况设定
        $objPHPExcel = new Spreadsheet();
        $objActSheetIndex = $objPHPExcel->setActiveSheetIndex(0);
        $objActSheet = $objPHPExcel->getActiveSheet(0);
        
        $cellkey = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K','L','M','N');


        // 处理导出的数据！
        $new_arr1 = [];
        foreach ($expTableData as $ks => $vs) {

            $new_arr1[$vs['id']]['id'] = $vs['id'];
            $new_arr1[$vs['id']]['proname'] = $vs['proname'];
            $new_arr1[$vs['id']]['company_name'] = $vs['company_name'];
            $new_arr1[$vs['id']]['statusname'] = $vs['statusname'];
            $new_arr1[$vs['id']]['area'] = $vs['area'];
            $new_arr1[$vs['id']]['height'] = $vs['height'];
            $new_arr1[$vs['id']]['address'] = $vs['address'];
            $new_arr1[$vs['id']]['fzname'] = $vs['fzname'];
            $new_arr1[$vs['id']]['lxtel'] = $vs['lxtel'];
            $new_arr1[$vs['id']]['score'] = $vs['score'];
            $new_arr1[$vs['id']]['yhcount'] = $vs['yhcount'];
            $new_arr1[$vs['id']]['checkname'] = $vs['checkname'];
            $new_arr1[$vs['id']]['checktime'] = $vs['checktime'];
            if (count($vs['CheckListDetails']) > 0) {
                $new_arr1[$vs['id']]['list'] = $vs['CheckListDetails'];
            } else {
                $new_arr1[$vs['id']]['list'][0]['topicname'] = '';
                $new_arr1[$vs['id']]['list'][0]['desc'] = '';
                $new_arr1[$vs['id']]['list'][0]['zhenggai'] = '';
            }
            $new_arr1[$vs['id']]['note_text'] = $vs['note_text'];
        }


        $newlist = [];
        // 重组数组
        foreach ($new_arr1 as $k => $v) {
            $temp = $v;
            $tempSix = $v['list'];
            $temp['list'] = '';
            foreach ($tempSix as $kk => $vv) {
                $temp['list'] = $vv;
                $newlist[] = $temp;
            }
        }

        $objActSheetIndex
            ->setCellValue('A1', 'ID')
            ->setCellValue('B1', '所属社区')
            ->setCellValue('C1', '场所名')
            ->setCellValue('D1', '异常状态')
            ->setCellValue('E1', '场所面积')
            ->setCellValue('F1', '建筑高度')
            ->setCellValue('G1', '场所地址')
            ->setCellValue('H1', '消防负责人')
            ->setCellValue('I1', '联系电话')
            ->setCellValue('J1', '得分')
            ->setCellValue('K1', '隐患条数')
            ->setCellValue('L1', '排查人员')
            ->setCellValue('M1', '排查时间')
            ->setCellValue('N1', '复检状态')
            ->setCellValue('O1', '隐患条款')
            ->setCellValue('P1', '不符合描述')
            ->setCellValue('Q1', '备注');


        $new_arr1 = array_values($new_arr1);
        $new_arr2 = array_values($new_arr1);
        foreach ($new_arr2 as $k => $v) {
            if (count($v['list']) == 0) {
                $countArr[] = 1;
            } else {
                $countArr[] = count($v['list']);
            }
        }
        
        if ($countArr) {
            $numArr = [];
            $start = 2;
            foreach ($countArr as $k => $v) {
                if ($v > 1) {
                    $end = ($start + $v) - 1;
                } else {
                    $end = $start;
                }
                $numArr[] = [
                    'start' => $start, //起始数
                    'end' => $end, //结束数
                ];
                $start = $end + 1;
            }
        }

        //print('<pre>');print_r($numArr);die;
        $overcellkey = [];


        //遍历字母
        foreach ($cellkey as $k => $v) {
            foreach ($numArr as $kk => $vv) {
                //拼接坐标
                $overcellkey[] = [
                    'start' => $v . $vv['start'], //合并开始坐标
                    'end' => $v . $vv['end'], //合并结束坐标
                ];
            }
        }
        if ($overcellkey) {
            foreach ($overcellkey as $k => $v) {
                //进行上下单元格合并。
                $objPHPExcel->getActiveSheet()->mergeCells($v['start'] . ':' . $v['end']);
            }
        }

        //$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $count = count($newlist);  //计算有多少条数据
        $new_arr1 = $newlist;
        for ($i = 2; $i <= $count + 1; $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $new_arr1[$i - 2]['id']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $new_arr1[$i - 2]['proname'])->getColumnDimension('B')->setWidth(30);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $new_arr1[$i - 2]['company_name'])->getColumnDimension('C')->setWidth(50);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $new_arr1[$i - 2]['statusname'])->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $new_arr1[$i - 2]['area'])->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $new_arr1[$i - 2]['height'])->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $new_arr1[$i - 2]['address'])->getColumnDimension('G')->setWidth(50);
            $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, $new_arr1[$i - 2]['fzname'])->getColumnDimension('H')->setWidth(15);
            $objPHPExcel->getActiveSheet()->setCellValue('I' . $i, $new_arr1[$i - 2]['lxtel'])->getColumnDimension('I')->setWidth(20);
            $objPHPExcel->getActiveSheet()->setCellValue('J' . $i, $new_arr1[$i - 2]['score'])->getColumnDimension('J')->setWidth(20);
            $objPHPExcel->getActiveSheet()->setCellValue('K' . $i, $new_arr1[$i - 2]['yhcount'])->getColumnDimension('K')->setWidth(20);
            if ($new_arr1[$i - 2]['list']['zhenggai'] == '未整改') {
                $color = 'B8002E';
            } else {
                $color = '00FF00';
            }
            
            $objPHPExcel->getActiveSheet()->setCellValue('L' . $i, $new_arr1[$i - 2]['checkname'])->getColumnDimension('L')->setWidth(15);
            $objPHPExcel->getActiveSheet()->setCellValue('M' . $i, $new_arr1[$i - 2]['checktime'])->getColumnDimension('M')->setWidth(30);
            $objPHPExcel->getActiveSheet()->setCellValue('N' . $i, $new_arr1[$i - 2]['list']['zhenggai'])->getStyle('N' . $i)->getFont()->getColor()->setRGB($color);
            $objPHPExcel->getActiveSheet()->setCellValue('O' . $i, $new_arr1[$i - 2]['list']['topicname'])->getColumnDimension('O')->setWidth(160);
            $objPHPExcel->getActiveSheet()->setCellValue('P' . $i, $new_arr1[$i - 2]['list']['desc'])->getColumnDimension('P')->setWidth(80);
            $objPHPExcel->getActiveSheet()->setCellValue('Q' . $i, $new_arr1[$i - 2]['note_text'])->getColumnDimension('Q')->setWidth(80);
        }


        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $fileName . '.Xlsx"');
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($objPHPExcel);
        $writer->save('php://output');
        //删除临时的sheet
        $objPHPExcel->disconnectWorksheets();
        unset($objPHPExcel);
        exit;
    }
}
